MICROSOFT SQL SERVER is a comprehensive database management platform that provides extensive management and development tools, a powerful extraction, transformation, and loading (ETL) tool, business intelligence and analysis services, and other capabilities. Among other improvements, the MICROSOFT WINDOWS .NET Framework Common Language Runtime (CLR) recently has been integrated into the SQL SERVER database.
The CLR is the heart of the MICROSOFT .NET Framework, and provides the execution environment for all NET code. Thus, code that runs within the CLR is referred to as “managed code.” The CLR provides various functions and services required for program execution, including just-in-time (JIT) compilation, allocating and managing memory, enforcing type safety, exception handling, thread management and security. The CLR is now loaded by SQL SERVER upon the first invocation of a .NET routine.
In previous versions of SQL SERVER, database programmers were limited to using Transact-SQL when writing code on the server side. Transact-SQL is an extension of the Structured Query Language (“SQL”) as defined by the International Standards Organization (ISO) and the American National Standards Institute (ANSI). Using Transact-SQL, database developers can create, modify and delete databases and tables, as well as insert, retrieve, modify and delete data stored in a database. Transact-SQL is specifically designed for direct structural data access and manipulation. While Transact-SQL excels at structural data access and management, it is not a full-fledged programming language as are VISUAL BASIC .NET and C#. For example, Transact-SQL does not support arrays, collections, for each loops, bit shifting or classes.
With the CLR integrated into the SQL SERVER database, database developers can now perform tasks that were impossible or difficult to achieve with Transact-SQL alone. Both VISUAL BASIC NET and C# are modern programming languages offering full support for arrays, structured exception handling, and collections. Developers can leverage CLR integration to write code that has more complex logic and is more suited for computation tasks using languages such as VISUAL BASIC .NET and C#. These programming languages offer object-oriented capabilities such as encapsulation, inheritance and polymorphism. Related code can be easily organized into classes and namespaces.
Managed code is better suited than Transact-SQL for number crunching and complicated execution logic, and features extensive support for many complex tasks, including string handling and regular expressions. With the functionality found in the .NET Framework Base Class Library (BCL), database developers have access to thousands of pre-built classes and routines which can be easily accessed from any stored procedure, trigger or user defined function.
Another benefit of managed code is type safety. Before managed code is executed, the CLR verifies that the code is safe. This process is known as “verification.” During verification, the CLR performs several checks to ensure that the code is safe to run. For example, the code is checked to ensure that no memory is read that has not be been written to. The CLR will also prevent buffer overflows.
When writing managed code, the deployment unit is called an assembly. An assembly is packaged as a dynamic link library (DLL). Managed DLL assemblies can be loaded into and hosted by SQL SERVER. The CREATE ASSEMBLY statement is used to register an assembly in the server. Here is an example:                CREATE ASSEMBLY YukonCLR        FROM ‘C:\MyDBApp\YukonCLR.dll’The FROM clause specifies the pathname of the assembly to load.        
SQL SERVER has traditionally supported “built-in” scalar types such as integer, floating point number, date, time, and character string. These built-in types also come with a set of built-in operations such as +, −, *, / as well as built-in functions over these types. These types, operations, and functions are “built-in” in the sense that they are implemented and packaged by the product and users cannot define their own types.
It would be desirable for a database system, such as SQL SERVER, to allow users to extend the type system of the database system, such that users could create new types that act as scalar types in the database system, but that contain more complex structure and behavior. For example, it may be desirable for a user to create a “Point” type consisting of X and Y coordinates.
The SQL standard and some database management system (DBMS) products have used the term “user-defined type” (UDT) to describe several forms of type extensibility. For example, the SQL-99 standard describes a “distinct type,” which is a type that can be defined by a user to have an internal representation that is a value of an existing SQL built-in data type. A distinct type may optionally share comparison and arithmetic operators, type conversions, and aggregate (column) functions (e.g., max, min, average) with an existing scalar type. The distinct type may allow constraints to be defined on its values. In addition, a distinct type may expose behaviors beyond those of an existing scalar type by defining new functions specific to the distinct type via user-defined functions. With respect to type checking, the distinct type and existing scalar types are considered to be different types.
The main advantage of distinct types is ease of definition. If the internal representation of the new type has a single data member that can be described with an existing built-in type and the built-in type already implements most of the behaviors required on the new type, then distinct types are an attractive alternative. The user does not have to worry about implementing the behaviors required to manage the on-disk storage of the type, constructors, comparison operators (used for ordering and indexing), arithmetic operators, and type conversion (casting) operators. The user only needs to choose what functionality of the underlying built-in type needs to be exposed on the distinct type and optionally add constraints on values or additional functions on the new type. Another advantage of distinct types is that all of the query processing available for built-in types, such as computation of histograms, can be readily used on columns of distinct types. A disadvantage of distinct types, however, is that they can not easily be used to create more complex types.
The SQL-99 standard also described a “structured type,” which is a type that can be defined by user and that has an internal representation that is a collection of data members, each of which may be of a different SQL built-in or user defined type. This is similar to the notion of a struct in C/C++. SQL-99 describes a style for defining structured types by which a user only needs to define the type in terms of its internal structure. The system automatically generates accessor and mutator functions on its data members, constructor, and functions to manage the on-disk representation of instances of the type.
The main advantages of structured types as defined in SQL are ease of definition of basic behaviors of the type, and flexibility to define more complex types. The disadvantage is complexity of definition of type-specific methods, which are usually defined via external functions written in a general-purpose programming language like C/C++. In order to define a structured type fully, the definer of the type needs to straddle a line between SQL and some other programming language.
While the distinct type and structured type features of SQL 99 offer some advantages to users in terms of enabling them to extend the existing scalar type system of a SQL database, there is a need for an improved system and method for enabling a user to extend the scalar type system of a database system through user-defined types that act as scalar types but that contain more complex structure and behavior. The present invention satisfies this need.